const pool = require('../config/db');

const getStores = async () => {
    const [rows] = await pool.execute('SELECT * FROM stores');
    return rows;
};

const getStoreById = async (id) => {
    console.log('[MODEL] 获取店铺详情:', { id });
    const [rows] = await pool.execute('SELECT * FROM stores WHERE id = ?', [id]);
    
    if (rows.length === 0) {
        console.log('[MODEL] 未找到店铺:', { id });
        return null;
    }
    
    const store = rows[0];
    console.log('[MODEL] 店铺详情:', { 
        id: store.id, 
        name: store.name, 
        hasLatitude: !!store.latitude,
        hasLongitude: !!store.longitude,
        latitude: store.latitude,
        longitude: store.longitude
    });
    
    return store;
};

const getStoreByOwnerId = async (ownerId) => {
    const [rows] = await pool.execute('SELECT * FROM stores WHERE owner_id = ?', [ownerId]);
    return rows[0];
};

// 获取附近的店铺
const getStoresByLocation = async (options) => {
    const { 
        latitude, 
        longitude, 
        radius = 5000, // 默认5公里
        type = 'all',
        sort = 'distance',
        asc = 0,
        keyword = '',
        page = 1,
        pageSize = 10
    } = options;
    
    console.log('[MODEL] 获取附近店铺:', { latitude, longitude, radius, type, sort, asc, keyword, page, pageSize });
    
    try {
        // 构建基础查询，使用Haversine公式计算距离
        let query = `
            SELECT 
                s.*,
                (
                    6371000 * acos(
                        cos(radians(?)) * cos(radians(s.latitude)) * cos(radians(s.longitude) - radians(?)) + 
                        sin(radians(?)) * sin(radians(s.latitude))
                    )
                ) AS distance
            FROM 
                stores s
            WHERE 
                s.is_approved = 1
        `;
        
        const params = [latitude, longitude, latitude];
        
        // 添加类型筛选
        if (type && type !== 'all') {
            query += ' AND s.type = ?';
            params.push(type);
        }
        
        // 添加关键词搜索
        if (keyword) {
            query += ' AND (s.name LIKE ? OR s.address LIKE ? OR s.description LIKE ?)';
            const likeKeyword = `%${keyword}%`;
            params.push(likeKeyword, likeKeyword, likeKeyword);
        }
        
        // 添加距离筛选
        query += ' HAVING distance <= ?';
        params.push(radius);
        
        // 添加排序
        if (sort === 'distance') {
            query += ` ORDER BY distance ${asc === 1 ? 'ASC' : 'DESC'}`;
        } else if (sort === 'rating') {
            query += ` ORDER BY s.rating ${asc === 1 ? 'ASC' : 'DESC'}`;
        } else if (sort === 'price') {
            query += ` ORDER BY s.price_level ${asc === 1 ? 'ASC' : 'DESC'}`;
        } else {
            query += ' ORDER BY distance ASC'; // 默认按距离排序
        }
        
        // 添加分页
        const offset = (page - 1) * pageSize;
        query += ' LIMIT ? OFFSET ?';
        params.push(parseInt(pageSize), offset);
        
        console.log('[MODEL] 执行查询:', { query, params });
        
        // 执行查询
        const [rows] = await pool.query(query, params);
        
        // 格式化距离显示
        const formattedResults = rows.map(store => ({
            ...store,
            distance: parseFloat((store.distance / 1000).toFixed(2)) // 转换为公里并保留两位小数
        }));
        
        // 获取总记录数（不考虑分页）
        let countQuery = `
            SELECT 
                COUNT(*) as total
            FROM 
                stores s,
                (SELECT 
                    (
                        6371000 * acos(
                            cos(radians(?)) * cos(radians(s.latitude)) * cos(radians(s.longitude) - radians(?)) + 
                            sin(radians(?)) * sin(radians(s.latitude))
                        )
                    ) AS distance
                FROM 
                    stores s
                WHERE 
                    s.is_approved = 1
        `;
        
        const countParams = [latitude, longitude, latitude];
        
        // 添加类型筛选
        if (type && type !== 'all') {
            countQuery += ' AND s.type = ?';
            countParams.push(type);
        }
        
        // 添加关键词搜索
        if (keyword) {
            countQuery += ' AND (s.name LIKE ? OR s.address LIKE ? OR s.description LIKE ?)';
            const likeKeyword = `%${keyword}%`;
            countParams.push(likeKeyword, likeKeyword, likeKeyword);
        }
        
        countQuery += ') as distances WHERE distance <= ?';
        countParams.push(radius);
        
        const [countResult] = await pool.query(countQuery, countParams);
        const total = countResult[0].total;
        
        console.log('[MODEL] 查询结果:', { count: formattedResults.length, total });
        
        return {
            list: formattedResults,
            pagination: {
                total,
                page: parseInt(page),
                pageSize: parseInt(pageSize),
                totalPages: Math.ceil(total / pageSize)
            }
        };
    } catch (error) {
        console.error('[MODEL] 获取附近店铺失败:', error);
        throw error;
    }
};

const createStore = async (storeData) => {
    const {
        ownerId,
        storeName,
        type,
        logo,
        contactPerson,
        contactPhone,
        openTime,
        closeTime,
        address,
        description
    } = storeData;

    const [result] = await pool.execute(
        `INSERT INTO stores (
            owner_id, name, type, logo, contact_person, contact_phone, 
            open_time, close_time, address, description
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
        [ownerId, storeName, type, logo, contactPerson, contactPhone, 
         openTime, closeTime, address, description]
    );

    return result.insertId;
};

const updateStore = async (storeId, storeData) => {
    const {
        storeName,
        type,
        logo,
        contactPerson,
        contactPhone,
        openTime,
        closeTime,
        address,
        description
    } = storeData;

    await pool.execute(
        `UPDATE stores SET 
            name = ?, 
            type = ?, 
            logo = ?, 
            contact_person = ?, 
            contact_phone = ?, 
            open_time = ?, 
            close_time = ?, 
            address = ?, 
            description = ?,
            updated_at = CURRENT_TIMESTAMP
        WHERE id = ?`,
        [storeName, type, logo, contactPerson, contactPhone, 
         openTime, closeTime, address, description, storeId]
    );
};

const updateStoreLogo = async (storeId, logoUrl) => {
    await pool.execute(
        'UPDATE stores SET logo = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?',
        [logoUrl, storeId]
    );
};

const getHotStores = async () => {
    const [rows] = await pool.execute('SELECT * FROM hot_stores JOIN stores ON hot_stores.store_id = stores.id');
    return rows;
};

const addHotStore = async (storeId) => {
    await pool.execute('INSERT INTO hot_stores (store_id) VALUES (?)', [storeId]);
};

const updateHotStore = async (id, data) => {
    await pool.execute('UPDATE hot_stores SET sort_order = ? WHERE id = ?', [data.sort_order, id]);
};

const removeHotStore = async (storeId) => {
    await pool.execute('DELETE FROM hot_stores WHERE store_id = ?', [storeId]);
};

// 获取商家申请列表
const getMerchantApplications = async (options = {}) => {
    const { status, page = 1, limit = 10 } = options;
    
    console.log('[MODEL] 获取商家申请列表:', { status, page, limit });
    
    // 检查merchant_applications表是否有经纬度字段
    try {
        const [columns] = await pool.query("SHOW COLUMNS FROM merchant_applications LIKE 'latitude'");
        const hasLatLng = columns.length > 0;
        
        // 构建查询
        let query = `
            SELECT 
                ma.id, 
                ma.user_id, 
                ma.business_name, 
                ma.address, 
                ${hasLatLng ? 'ma.latitude, ma.longitude,' : ''}
                ma.contact_phone, 
                ma.contact_person, 
                ma.store_type, 
                ma.status, 
                ma.apply_time, 
                ma.review_time,
                ma.reject_reason, 
                u.phone, 
                u.name as user_name, 
                u.email
            FROM merchant_applications ma
            LEFT JOIN users u ON ma.user_id = u.id
        `;
        
        const params = [];
        
        // 添加状态过滤
        if (status) {
            query += ' WHERE ma.status = ?';
            params.push(status);
        }
        
        // 计算总记录数
        const countQuery = `SELECT COUNT(*) as total FROM merchant_applications ${status ? 'WHERE status = ?' : ''}`;
        const [countResult] = await pool.execute(countQuery, status ? [status] : []);
        const total = countResult[0].total;
        
        console.log('[MODEL] 总记录数:', { total });
        
        // 添加排序和分页
        query += ' ORDER BY ma.apply_time DESC';
        
        // 使用字符串拼接而不是参数化，避免参数类型问题
        const limitValue = parseInt(limit);
        const offsetValue = parseInt((page - 1) * limit);
        query += ` LIMIT ${limitValue} OFFSET ${offsetValue}`;
        
        console.log('[MODEL] 执行查询:', { query, params });
        
        // 执行查询
        const [rows] = await pool.query(query, params);
        
        console.log('[MODEL] 查询结果:', { count: rows.length, total });
        
        return {
            list: rows,
            total,
            page: Number(page),
            limit: Number(limit)
        };
    } catch (error) {
        console.error('[MODEL] 查询商家申请列表失败:', error);
        throw error;
    }
};

// 获取商家申请详情
const getMerchantApplicationDetail = async (id) => {
    console.log('[MODEL] 获取商家申请详情:', { id });
    
    if (!id || id === 'undefined') {
        throw new Error('商家申请不存在');
    }
    
    try {
        // 检查merchant_applications表是否有经纬度字段
        const [columns] = await pool.query("SHOW COLUMNS FROM merchant_applications LIKE 'latitude'");
        const hasLatLng = columns.length > 0;
        
        // 只从merchant_applications表查询
        const [rows] = await pool.execute(
            `SELECT ma.*, u.phone, u.name as user_name, u.email
             FROM merchant_applications ma
             LEFT JOIN users u ON ma.user_id = u.id
             WHERE ma.id = ?`,
            [id]
        );
        
        if (rows.length === 0) {
            throw new Error('商家申请不存在');
        }
        
        console.log('[MODEL] 找到商家申请详情');
        return rows[0];
    } catch (error) {
        console.error('[MODEL] 获取商家申请详情失败:', error);
        throw error;
    }
};

// 审核商家申请
const auditMerchantApplication = async (id, { status, rejectReason }) => {
    console.log('[MODEL] 审核商家申请', { id, status, rejectReason });
    
    if (!id || id === 'undefined') {
        throw new Error('商家申请不存在');
    }
    
    // 开始事务
    await pool.query('START TRANSACTION');
    
    try {
        // 从merchant_applications表获取申请
        const [applications] = await pool.execute(
            'SELECT * FROM merchant_applications WHERE id = ?',
            [id]
        );
        
        if (applications.length === 0) {
            throw new Error('商家申请不存在');
        }
        
        const application = applications[0];
        
        // 更新申请状态
        await pool.execute(
            `UPDATE merchant_applications 
             SET status = ?, reject_reason = ?, review_time = NOW() 
             WHERE id = ?`,
            [status, rejectReason || null, id]
        );
        
        // 如果审核通过，创建店铺
        if (status === 'approved') {
            // 转换店铺类型
            const storeType = convertStoreType(application.store_type);
            
            // 检查是否已存在相同手机号的用户
            const [existingUsers] = await pool.execute(
                'SELECT id FROM users WHERE phone = ?',
                [application.contact_phone]
            );
            
            let ownerId;
            
            if (existingUsers.length > 0) {
                // 如果用户已存在，更新角色为store
                ownerId = existingUsers[0].id;
                await pool.execute(
                    'UPDATE users SET role = "store", name = ? WHERE id = ?',
                    [application.business_name, ownerId]
                );
                console.log('[MODEL] 更新现有用户为商家角色', { userId: ownerId });
            } else {
                // 创建新用户，使用联系电话作为手机号，商家名称作为用户名
                const randomPassword = Math.random().toString(36).slice(-8);
                const bcrypt = require('bcryptjs');
                const hashedPassword = await bcrypt.hash(randomPassword, 10);
                
                const [userResult] = await pool.execute(
                    `INSERT INTO users (
                        phone, name, role, password, created_at
                    ) VALUES (?, ?, "store", ?, NOW())`,
                    [application.contact_phone, application.business_name, hashedPassword]
                );
                
                ownerId = userResult.insertId;
                console.log('[MODEL] 创建新商家用户成功', { userId: ownerId, phone: application.contact_phone });
            }
            
            // 检查是否有经纬度字段
            const hasLatLng = 'latitude' in application && 'longitude' in application;
            
            // 创建店铺
            let storeQuery, storeParams;
            
            if (hasLatLng) {
                storeQuery = `
                    INSERT INTO stores (
                        owner_id, name, type, address, latitude, longitude, contact_person, contact_phone, is_approved
                    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, 1)
                `;
                storeParams = [
                    ownerId,
                    application.business_name,
                    storeType,
                    application.address,
                    application.latitude || null,
                    application.longitude || null,
                    application.contact_person,
                    application.contact_phone
                ];
            } else {
                storeQuery = `
                    INSERT INTO stores (
                        owner_id, name, type, address, contact_person, contact_phone, is_approved
                    ) VALUES (?, ?, ?, ?, ?, ?, 1)
                `;
                storeParams = [
                    ownerId,
                    application.business_name,
                    storeType,
                    application.address,
                    application.contact_person,
                    application.contact_phone
                ];
            }
            
            const [result] = await pool.execute(storeQuery, storeParams);
            
            console.log('[MODEL] 商家申请审核通过，创建店铺成功', { storeId: result.insertId, ownerId });
        }
        
        // 提交事务
        await pool.query('COMMIT');
        
        return { success: true };
    } catch (error) {
        // 回滚事务
        await pool.query('ROLLBACK');
        console.error('[MODEL] 商家申请审核失败', { error: error.message, stack: error.stack });
        throw error;
    }
};

// 转换店铺类型的辅助函数
const convertStoreType = (sourceType) => {
    // 转换店铺类型
    let storeType = '美食';
    if (sourceType === '住宿') storeType = '住宿';
    else if (sourceType === '购物') storeType = '购物';
    else if (sourceType === '维修') storeType = '维修';
    else if (sourceType === '农产品' || sourceType === '农村特种行业') storeType = '农产品';
    return storeType;
};

module.exports = { 
    getStores, 
    getStoreById,
    getStoreByOwnerId,
    getStoresByLocation,
    createStore,
    updateStore,
    updateStoreLogo,
    getHotStores, 
    addHotStore, 
    updateHotStore, 
    removeHotStore,
    getMerchantApplications,
    getMerchantApplicationDetail,
    auditMerchantApplication,
    convertStoreType
};